﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;

namespace DAL
{    
  public  class AbandonDAL
    {

      /// <summary>
        /// 获取报废出库列表
      /// </summary>
      /// <returns></returns>
      public DataSet GetAbandonList()
      {
          DataSet ds = new DataSet();
          StringBuilder commandText = new StringBuilder();
       
     
          commandText.Append(" select ROW_NUMBER() over (order by b.FBillNo) Num ,b.FBillNo,b.Fdate,b.Fry,d.NAME,b.Total,b.LeftQty from ( ");
          commandText.Append(" select a.FBillNo,CONVERT(varchar(10),a.Fdate,23) Fdate ,a.Fry,isnull(COUNT(0),0) Total,   	");
          commandText.Append("    sum(case flag when 0 then 1 else 0 end ) LeftQty ");
          commandText.Append(" from t_AbandonDetail a   group by a.FBillNo,a.Fry,CONVERT(varchar(10),a.Fdate,23) ");
          commandText.Append(" )b ");
          commandText.Append("  left join sys_master d on d.ID= b.Fry ");
          commandText.Append("  left join t_Abandoned e on e.FBillNo = b.FBillNo   	  ");
          commandText.Append("  where  e.States = '2'  ");
          
          try
           {
               DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
               ds = DbHelperSQL.Query(commandText.ToString());
           }
           catch (Exception ex)
           {
               throw ex;
           }
          return ds;
      }

      /// <summary>
      /// 报废明细
      /// </summary>
      /// <param name="FBillNo"></param>
      /// <param name="Ry"></param>
      /// <returns></returns>
      public DataSet GetAbandonedDetail(string FBillNo, string Ry,  string date)
      {
          DataSet ds = new DataSet();
          StringBuilder commandText = new StringBuilder();

          commandText.Append(" select b.FInterID,b.PkgsBarCode,b.PkgsID,a.PlaceBarCode,");
          commandText.Append("  f.floor,(a.Fname +'层'+  CAST(f.Floor AS nvarchar)) PlaceName ,b.FSku,");
          commandText.Append("   b.StickLen,b.FQty,e.FItemName,e.AttrValue    from t_AbandonDetail b   ");
          commandText.Append("   left join  t_StockPlace a on b.PosiID = a.FSPID   ");
          commandText.Append("   left join t_ICItemSku e on e.FSku = b.FSku   ");
          commandText.Append("   left join stk_Package f on f.PkgsBarCode = b.PkgsBarCode    ");
          commandText.Append("   where   flag = 0 and FBillNo ='" + FBillNo + "' and b.Fry = '" + Ry + "' ");
          commandText.Append("   and CONVERT(varchar(100), b.Fdate, 23)  = '" + date + "' ");
          commandText.Append("   order by FBillNo desc,f.Floor desc");

          
          try
          {
              DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
              ds = DbHelperSQL.Query(commandText.ToString());
          }
          catch (Exception ex)
          {
              throw ex;
          }
          return ds;
      }

      /// <summary>
      /// 查询报废剩余数量
      /// </summary>
      /// <param name="FBillNo"></param>
      /// <param name="Ry"></param>
      /// <returns></returns>  
      public int GetAbandonLeftQty(string FBillNo, string Ry, string date)
      {
          int i = 0;
          DataTable dt = new DataTable();
          StringBuilder sql = new StringBuilder();
     
          sql.Append(" select COUNT(0) num from t_AbandonDetail where FBillNo ='"+FBillNo+"'");
          sql.Append(" and CONVERT(varchar(100), Fdate, 23) =  '" + date + "' and Fry ='" + Ry + "' and flag=0 ");
          try
          {
              DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
              dt = DbHelperSQL.QueryTable(sql.ToString());
              if (dt != null)
              {
                  foreach (DataRow dr in dt.Rows)
                  {
                      i = Convert.ToInt32(dr["num"].ToString());
                  }
              }

          }
          catch (Exception ex)
          {
              throw ex;
          }
          return i;
      }

      /// <summary>
      ///取得条码的信息
      /// </summary>
      /// <param name="PkgsBarCode"></param>
      /// <param name="PlaceBarCode"></param>
      /// <param name="PlaceName"></param>
      /// <param name="Fsku"></param>
      /// <param name="StickLen"></param>
      /// <param name="Stick"></param>
      /// <param name="FQty"></param>
      /// <param name="dc"></param>
      /// <returns></returns>
      public bool GetCode(string PkgsBarCode, out string PlaceBarCode, out string PlaceName, out string Floor, out string Fsku, out string StickLen, out string Stick, out string FQty, out string dc)
      {
          bool result = false;
          StringBuilder commandText = new StringBuilder();
          DataSet ds = new DataSet();
          PlaceBarCode = ""; Floor = "";
          PlaceName = ""; Fsku = "";
          StickLen = ""; Stick = "";
          FQty = ""; dc = "";
          commandText.Append(" select a.PkgsBarCode,b.PlaceBarCode,(b.Fname +'层'+  CAST(a.Floor AS nvarchar)) PlaceName,a.Floor,a.ItemSku, ");
          commandText.Append("  a.StickLen,a.Stick,a.FQty, ");
          commandText.Append("  case SUBSTRING(a.HelperCode,1,1)  when 'A' then '1'  when 'B' then  '2'     end dc      ");
          commandText.Append("  from stk_Package a  ");
          commandText.Append("  left join t_StockPlace b on a.PosiID = b.FSPID  ");
          commandText.Append("  left join v_bdc c on a.PkgsID = c.PkgsID ");
          commandText.Append("  where PkgsBarCode ='" + PkgsBarCode + "'  ");

       
          try
          {
              DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
              ds = DbHelperSQL.Query(commandText.ToString());
          }
          catch (Exception ex)
          {
              throw ex;
          }
          if (ds != null)
          {
              result = true;

              foreach (DataRow dr in ds.Tables[0].Rows)
              {
                   PlaceBarCode = dr["PlaceBarCode"].ToString().Trim();
                  PlaceName = dr["PlaceName"].ToString().Trim();
                  Floor = dr["Floor"].ToString().Trim();
                  Fsku = dr["ItemSku"].ToString().Trim();
                  StickLen = dr["StickLen"].ToString().Trim();
                  Stick = dr["Stick"].ToString().Trim();
                  FQty = dr["FQty"].ToString().Trim();
                  dc = dr["dc"].ToString().Trim();
              }
          }
          return result;
      }


      /// <summary>
      /// 更新报废出库
      /// </summary>
      /// <param name="PkgsBarCode"></param>
      /// <param name="PkgsID"></param>
      /// <returns></returns>
      public int UpdateAbandonedDetail(string PkgsBarCode, string PkgsID, string uid)
      {
          int i = 0;
          StringBuilder sql = new StringBuilder();
         string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

         sql.Append("	update t_AbandonDetail set flag = 1,FinishID = '" + uid + "',FinishDate ='" + time + "' where  PkgsBarCode = '" + PkgsBarCode + "' and flag = '0'");

          try
          {
              DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
              i = DbHelperSQL.ExecuteSql(sql.ToString());
          }
          catch (Exception ex)
          {
              throw ex;
          }
          return i;
      }

      /// <summary>
      /// 完成出库报废
      /// </summary>
      /// <param name="FBillNo"></param>
      /// <param name="txt"></param>
      /// <returns></returns>
      public bool FinishAbandoned(string FBillNo, out string txt)
 {
         txt ="";
        bool lb_result = false;
        DataTable dt = new DataTable();
	    List<string> commandText = new List<string>();
        bool result = CheckAbandoned(FBillNo,out txt);
        if(result == true)
         {
             dt = GetAbandonedPkgsID(FBillNo);
             if(dt != null)
             {
				  foreach (DataRow dr in dt.Rows)
				 {
				   string PkgsID = dr["PkgsID"].ToString(); if (PkgsID != null && PkgsID != "") { } else { return false; }
                   string PkgsBarCode = dr["PkgsBarCode"].ToString(); if (PkgsBarCode != null && PkgsBarCode != "") { } else { return false; }

                   commandText.Add("update stk_Package set  ScrapBillNo ='" + FBillNo + "' ,PkgsStatus = '6' where PkgsBarCode  ='" + PkgsBarCode + "' and PkgsStatus ='1'");
				  }
             }
             commandText.Add(" update t_Abandoned set States ='4'  where FBillNo = '" + FBillNo + "' and  States ='2'");
             commandText.Add(" update t_AbandonedEntry  set States ='4'  where FBillNo = '" + FBillNo + "' and  States ='2' ");
		    try{    
				DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
				int i = DbHelperSQL.ExecuteSqlTran(commandText);
				if (i > 0)
				{
					lb_result = true;
				}
			}
			catch(Exception ex)
            {
            throw ex;
            }
            }
            return lb_result;
  }

     public bool CheckAbandoned(string FBillNo, out string txt)
     {
          txt ="";
         if(IsCheckAbandoned(FBillNo)){} else {txt="其他出库单未审核";return false;}
         if (IsQtyAbandoned(FBillNo)) { } else { txt = "该报废出库单还有剩余数量未确认"; return false; }
         return true;
     }

      /// <summary>
      /// 判断该编号是否被保存 state=2审核 
      /// </summary>
      /// <param name="FBillNo"></param>
      /// <returns></returns>
     public bool IsCheckAbandoned(string FBillNo) 
     {
      bool result = false;
         StringBuilder sql = new StringBuilder();
         DataTable dt = new DataTable();
          string ls_states = "";

         sql.Append("select States from t_Abandoned where  FBillNo  = '" + FBillNo + "'");

          try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    ls_states = dr[0].ToString().Trim();
                }
                if (ls_states == "2") {  } else { return false; }
            }
            return true;         
     }

      /// <summary>
      /// 判断是否出库完成
      /// </summary>
      /// <param name="FBillNo"></param>
      /// <returns></returns>
     public bool IsQtyAbandoned(string FBillNo)
     {
         StringBuilder sql = new StringBuilder();
         DataTable dt = new DataTable();
         int num=0;

         sql.Append("select COUNT(0) num from t_AbandonDetail where flag =0 and FBillNo ='"+FBillNo+"' ");
     
         try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    num =Convert.ToInt32(dr[0].ToString().Trim()) ;
                }
                if (num == 0) {  } else { return false; }
            }
            return true;   
     }
      /// <summary>
      /// 判断出库的条码、条码内码
      /// </summary>
      /// <param name="FBillNo"></param>
      /// <returns></returns>
    public DataTable GetAbandonedPkgsID(string FBillNo)
    {
        DataTable dt = new DataTable();
        StringBuilder sql = new StringBuilder();
        sql.Append("select PkgsBarCode,PkgsID from t_AbandonDetail where FBillNo ='" + FBillNo + "' and flag = '1'");

         try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.QueryTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
    }

    }
}
